package org.sunsys.qa.user.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.sunsys.qa.datasource.DataSourceProvider;
import org.sunsys.qa.user.User;
import org.sunsys.qa.user.UserRegistrationDto;
import org.sunsys.qa.user.exceptions.UserIdAlreadyExistedException;
import org.sunsys.qa.user.exceptions.UserRegistrationException;
import org.sunsys.qa.user.exceptions.UserValidationException;

public class UserDaoImpl implements UserDao {

	private Logger logger = Logger.getLogger(UserDaoImpl.class.getName());
	
	public UserDaoImpl() {
	}
	
	@Override
	public void registerUser(UserRegistrationDto registrationDto) throws UserIdAlreadyExistedException, UserRegistrationException {
		
		/* get DataSource */
		DataSource dataSource = DataSourceProvider.getInstance().getDataSource();
		
		Connection con = null;
		PreparedStatement preparedStatement = null;
		
		//id VARCHAR(50), name VARCHAR(50), password VARCHAR(50), emailId VARCHAR(50), dateOfBirth Date, dateOfRegistration DATE
		String query = "insert into qadatabase.User values(?, ?, ?, ?, ?, ?)";
		try {
			con = dataSource.getConnection();
			Date dob = new Date(registrationDto.getDateOfBirth().getTime());
			Date dateOfRegistration = new Date(registrationDto.getDateOfRegistration().getTime());

			preparedStatement = con.prepareStatement(query);
			preparedStatement.setString(1, registrationDto.getUserId());
			preparedStatement.setString(2, registrationDto.getUserName());
			preparedStatement.setString(3, registrationDto.getPassword());
			preparedStatement.setString(4, registrationDto.getMailId());
			preparedStatement.setDate(5, dob);
			preparedStatement.setDate(6, dateOfRegistration);
			
			int result = preparedStatement.executeUpdate();
			logger.debug("User Registration query execution result:"+result);
			
			//TODO have to write code to throw UserIdAlreadyExistedException
		}catch(SQLException e1) {
			logger.warn("SQL Exception while registing user", e1);
			throw new UserRegistrationException("SQL Exception while registing user", e1);
		}finally{
			try {
				if(preparedStatement!= null) {
					preparedStatement.close();
				}
			}catch(SQLException e2){
			}
			try {
				if(con!= null) {
					con.close();
				}
			}catch(SQLException e2){
			}
		}
	}

	public boolean doesUserExists(User user) throws UserValidationException {
		/* get DataSource */
		DataSource dataSource = DataSourceProvider.getInstance().getDataSource();
		
		Connection con = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		//id VARCHAR(50), name VARCHAR(50), password VARCHAR(50), emailId VARCHAR(50), dateOfBirth Date, dateOfRegistration DATE
		String query = "select count(*) from User where id = ? and password = ?";
		try {
			con = dataSource.getConnection();
			preparedStatement = con.prepareStatement(query);
			preparedStatement.setString(1, user.getUserId());
			preparedStatement.setString(2, user.getPassword());
			/* execute query and get the result set */
			resultSet = preparedStatement.executeQuery();
			/* get the number of records count */
			int recordsCount = resultSet.getInt(1);
			
			/* return true, if the records count is grater than 0 */
			return recordsCount >0; 
			
		}catch(SQLException e1) {
			throw new UserValidationException("Exceltion while validating user", e1);
		}finally{
			try {
				if(resultSet!= null) {
					resultSet.close();
				}
			}catch(SQLException e2){
			}
			try {
				if(preparedStatement!= null) {
					preparedStatement.close();
				}
			}catch(SQLException e2){
			}
			try {
				if(con!= null) {
					con.close();
				}
			}catch(SQLException e2){
			}
		}
	}

}
